# -*- coding: utf-8 -*-
# paho mqtt 中文文档 https://blog.csdn.net/weixin_41656968/article/details/80848542 官网文档 https://pypi.org/project/paho-mqtt/
# pymysql 中文文档 https://www.cnblogs.com/Equilibrium/p/12170365.html

import json
import time

import paho.mqtt.client as mqtt
import pymysql


# 数据库操作
def mysql(sql):
    # 建立连接 获取游标
    conn = pymysql.connect(host='www.zdiml.xyz',
                           port=3306,
                           user='mqtt',
                           password='zucc511',
                           database='mqtt')
    cur = conn.cursor()
    print("\n--------------------connect----------------------")
    try:
        # 执行语句
        print(sql)
        cur.execute(sql)
        # 保存执行结果
        rs = cur.fetchall()
        print("--------------------success---------------------")
    except Exception as e:
        return e
    finally:
        # 提交事务
        conn.commit()
        # 关闭游标 关闭连接
        cur.close()
        conn.close()
    return rs


def is_json(myjson):
    try:
        json.loads(myjson)
    except ValueError:
        return False
    return True


def json_sql(table, key_s, msg_dict):
    val_s = []
    sql = "insert into `%s`(" % table
    for key in key_s[0:-1]:
        sql += '`%s`,' % key
        val_s.append(msg_dict[key])
    sql += '`%s`)values(' % key_s[-1]
    for val in val_s:
        sql += '\'%s\',' % val
    sql += '\'%s\')' % msg_dict[key_s[-1]]
    return sql


# 收到消息以后 回调函数
def on_message(client, userdata, msg):
    payload_str = str(msg.payload.decode('utf-8'))

    if "close" != payload_str:
        if msg.topic == "zyy/dht11/send":
            if is_json(payload_str):
                print(
                    mysql(
                        json_sql('DHT11', [
                            'stu_order', 'stu_name', 'tty_name', 'temperature',
                            'humidity', 'username'
                        ], json.loads(payload_str))))
            else:
                print(payload_str)
        else:
            if is_json(payload_str):
                payload_dict = json.loads(payload_str)
                sql = "insert into temp(`name`, `how`, `what`, `when`)values('%s', '%s', '%s', '%s')" % (
                    payload_dict['name'], payload_dict['how'],
                    payload_dict['what'],
                    time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
            else:
                sql = "insert into IOT_ZYY(Topic, Qos, Payload, UpdateTime) values('%s', %d, '%s','%s')" % (
                    msg.topic, msg.qos, payload_str,
                    time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()))
            print(mysql(sql))


def on_disconnect(client, userdata, rc):
    if rc != 0:
        print("Unexpected disconnection %s" % rc)


if __name__ == "__main__":
    # -------------------------------------------------------- #
    HOST = "47.93.197.229"
    PORT = 8083  # websockets PORT
    # PORT = 1883 # tcp PORT
    client_id = "zyy_python"
    topics = ["ZUCC/ZYY"]
    # -------------------------------------------------------- #
    client = mqtt.Client(client_id, transport="websockets")
    # client = mqtt.Client(client_id, transport="tcp")
    # -------------------------------------------------------- #
    client.on_message = on_message
    client.on_disconnect = on_disconnect
    client.connect(HOST, PORT, 60)
    # -------------------------------------------------------- #
    for item in topics:
        client.subscribe(item, 1)
    # -------------------------------------------------------- #
    print("监听开始")
    client.loop_forever()
    # -------------------------------------------------------- #


def store():
    json_sql(
        'DHT11', [
            'stu_order', 'stu_name', 'tty_name', 'temperature', 'humidity',
            'username'
        ], {
            'stu_order': 31801146,
            'stu_name': '章一砚',
            'tty_name': '百元ESP8266_1',
            'temperature': 12.3,
            'humidity': 0.70,
            'username': 'IOT Lab 9'
        })
    "insert into `DHT11`(`stu_order`,`stu_name`,`tty_name`,`temperature`,`humidity`,`username`)values('31801146','章一砚','百元ESP8266_1','12.3','0.7','IOT Lab 9')"
